using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public BooklkivideoVO getBooklkivideo(int? idBookLkiVideo, string nome)
		{
			BooklkivideoVO booklkivideoVO = null;
			List<BooklkivideoVO> listBooklkivideoVOs = getBooklkivideos(idBookLkiVideo, nome, null);
			if (listBooklkivideoVOs != null && listBooklkivideoVOs.Count > 0)
			{
				booklkivideoVO = listBooklkivideoVOs[0];
			}
			return booklkivideoVO;
		}

		public BooklkivideoVO getBooklkivideo(int? idBookLkiVideo)
		{
			return getBooklkivideo(idBookLkiVideo, null);
		}

		public List<BooklkivideoVO> getBooklkivideos()
		{
			return getBooklkivideos(null, null, null);
		}

		public List<BooklkivideoVO> getBooklkivideos(int? idBookLkiVideo)
		{
			return getBooklkivideos(idBookLkiVideo, null, null);
		}

		public List<BooklkivideoVO> getBooklkivideos(int? idBookLkiVideo, string nome)
		{
			return getBooklkivideos(idBookLkiVideo, nome, null);
		}

		public List<BooklkivideoVO> getBooklkivideos(int? idBookLkiVideo, string nome, int? idBookLki)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT blv.idBookLkiVideo as blv_idBookLkiVideo, blv.idBookLki as blv_idBookLki, blv.url as blv_url, blv.nome as blv_nome, blv.cadastro as blv_cadastro, blv.cadastradoPor as blv_cadastradoPor, blv.edicao as blv_edicao, blv.editadoPor as blv_editadoPor  ");
			query.AppendLine("FROM booklkivideo as blv LEFT OUTER JOIN booklki as blk on blv.idBookLki=blk.idBookLki  ");
			if (idBookLkiVideo != null)
			{
				query.AppendLine(where + "blv.idBookLkiVideo=@idBookLkiVideo ");
				where = "AND ";
			}
			if (idBookLki != null)
			{
				query.AppendLine(where + "blv.idBookLki=@idBookLki ");
				where = "AND ";
			}
			if (nome != null)
			{
				query.AppendLine(where + "blv.nome LIKE @nome ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY blv.nome ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idBookLkiVideo!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookLkiVideo", idBookLkiVideo));
			if (idBookLki!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookLki", idBookLki));
			if (nome!= null) cmd.Parameters.Add(getParameter(cmd, "@nome", "%"+nome+"%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BooklkivideoVO> listBooklkivideo = new List<BooklkivideoVO>();

				while (reader.Read())
				{
					BooklkivideoVO booklkivideoVO = BooklkivideoVO.CreateVO();
					booklkivideoVO.idBookLkiVideo = reader.GetInt32(reader.GetOrdinal("blv_idBookLkiVideo"));
					booklkivideoVO.idBookLki = reader.GetInt32(reader.GetOrdinal("blv_idBookLki"));
					booklkivideoVO.url = reader.GetString(reader.GetOrdinal("blv_url"));
					booklkivideoVO.nome = reader.GetString(reader.GetOrdinal("blv_nome"));
					booklkivideoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("blv_cadastro"));
					booklkivideoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("blv_cadastradoPor"));
					booklkivideoVO.edicao = reader.GetDateTime(reader.GetOrdinal("blv_edicao"));
					booklkivideoVO.editadoPor = reader.GetString(reader.GetOrdinal("blv_editadoPor"));


					listBooklkivideo.Add(booklkivideoVO);
				}
				reader.Close();
				return listBooklkivideo;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setBooklkivideo(BooklkivideoVO booklkivideoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE booklkivideo ");
			query.AppendLine("SET idBookLki=@idBookLki, url=@url, nome=@nome,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idBookLkiVideo=@idBookLkiVideo ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiVideo", booklkivideoVO.idBookLkiVideo));
			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkivideoVO.idBookLki));
			cmd.Parameters.Add(getParameter(cmd, "@url", booklkivideoVO.url));
			cmd.Parameters.Add(getParameter(cmd, "@nome", booklkivideoVO.nome));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkivideoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkivideoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkivideoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkivideoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newBooklkivideo(BooklkivideoVO booklkivideoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO booklkivideo ");
			query.AppendLine("  ( idBookLki, url, nome, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @idBookLki, @url, @nome, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLki", booklkivideoVO.idBookLki));
			cmd.Parameters.Add(getParameter(cmd, "@url", booklkivideoVO.url));
			cmd.Parameters.Add(getParameter(cmd, "@nome", booklkivideoVO.nome));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", booklkivideoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", booklkivideoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", booklkivideoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", booklkivideoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('booklkivideo') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delBooklkivideo(BooklkivideoVO booklkivideoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM booklkivideo ");
			query.AppendLine("WHERE idBookLkiVideo=@idBookLkiVideo ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookLkiVideo", booklkivideoVO.idBookLkiVideo));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountBooklkivideoxIdBookLki(int? idBookLki)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idBookLki) as ocorrencias FROM booklkivideo WHERE IdBookLki=@IdBookLki ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdBookLki", idBookLki));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
	}
}
